using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text.RegularExpressions;
using System.Text;
using System.Security.Permissions;
using MagnoliaTableAdapters;


public partial class ReportDailyRun : System.Web.UI.Page
{
    static DataTable dtResults = new DataTable();
    static string pageResultStr = "";
    static string reportResultStr = "";

    protected override void Render(HtmlTextWriter writer)
    {
        // Write the HTML into this string builder
        StringBuilder sb = new StringBuilder();
        HtmlTextWriter hWriter = new HtmlTextWriter(new StringWriter(sb));
        base.Render(hWriter);

        // Store to a string
        pageResultStr = sb.ToString();

        // Write it back to the server's HTTP Stream -// *** skip this if you don't want to display the rendered content
        writer.Write(pageResultStr);
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        Page.Title = ConfigurationManager.AppSettings["PageTitle"] + " - Reporting - Sign Usage";
        
        // Add calendar to date fields
        TextBox_DateFrom.Attributes.Add("onfocus", "this.select();lcs(this);");
        TextBox_DateFrom.Attributes.Add("onclick", "event.cancelBubble=true;this.select();lcs(this);");
        TextBox_DateTo.Attributes.Add("onfocus", "this.select();lcs(this);");
        TextBox_DateTo.Attributes.Add("onclick", "event.cancelBubble=true;this.select();lcs(this);");
        DDList_Channels.Style.Add("width", "100%");
        DDList_Properties.Style.Add("width", "100%");
        //DDList_Clients.Style.Add("width", "100%");
        DDList_MediaTypes.Style.Add("width", "100%");
        TextBox_DurationFrom.Attributes.Add("onchange", "validateDurationInput(this);");
        TextBox_DurationTo.Attributes.Add("onchange", "validateDurationInput(this);");

        Btn_RunReport.Attributes.Add("onfocus", "cs_blur();");
        Btn_Reset.Attributes.Add("onfocus", "cs_blur();");
        Btn_Print.Attributes.Add("onclick", "window.print();");
        Btn_ExportCSV.Attributes.Add("onclick", "showExportPopup();");

        Lbl_Messages.Text = "Please provide your report parameters and click \"Run Report\" to view the results";

        Lbl_ReportTitle.Style["float"] = "none";
        Lbl_ReportTitle.Style["padding-bottom"] = "5px";
        Lbl_ReportTitle.Style["display"] = "block";

        if (!Page.IsPostBack)
        {
            ExportFileName.Value = Convert.ToString(Page.Title) + ".doc";

            // Load channel list
            Magnolia.CHANNELDataTable Channels = new Magnolia.CHANNELDataTable();
            CHANNELTableAdapter ChannelAdapter = new CHANNELTableAdapter();
            ChannelAdapter.FillChannelsSorted(Channels);

            DDList_Channels.DataSource = Channels;
            DDList_Channels.DataTextField = Channels.NameColumn.ColumnName;
            DDList_Channels.DataValueField = Channels.IDColumn.ColumnName;
            DDList_Channels.DataBind();

            ListItem channelDefault = new ListItem();
            channelDefault.Value = "0";
            channelDefault.Text = "All Channels";
            DDList_Channels.Items.Insert(0, channelDefault);
            DDList_Channels.SelectedIndex = -1;

            // Load property list
            Magnolia.PROPERTYDataTable Properties = new Magnolia.PROPERTYDataTable();
            PROPERTYTableAdapter PropertyAdapter = new PROPERTYTableAdapter();
            PropertyAdapter.Fill(Properties);

            DDList_Properties.DataSource = Properties;
            DDList_Properties.DataTextField = Properties.ValueColumn.ColumnName;
            DDList_Properties.DataValueField = Properties.IDColumn.ColumnName;
            DDList_Properties.DataBind();

            ListItem propertyDefault = new ListItem();
            propertyDefault.Value = "0";
            propertyDefault.Text = "All Properties";
            DDList_Properties.Items.Insert(0, propertyDefault);
            DDList_Properties.SelectedIndex = -1;

            // Load client list
            //Magnolia.CLIENTDataTable Clients = new Magnolia.CLIENTDataTable();
            //CLIENTTableAdapter ClientAdapter = new CLIENTTableAdapter();
            //ClientAdapter.Fill(Clients);

            //DDList_Clients.DataSource = Clients;
            //DDList_Clients.DataTextField = Clients.ValueColumn.ColumnName;
            //DDList_Clients.DataValueField = Clients.IDColumn.ColumnName;
            //DDList_Clients.DataBind();

            //ListItem clientDefault = new ListItem();
            //clientDefault.Value = "0";
            //clientDefault.Text = "All Clients";
            //DDList_Clients.Items.Insert(0, clientDefault);
            //DDList_Clients.SelectedIndex = -1;

            // Load media type list
            Magnolia.MEDIATYPEDataTable MediaTypes = new Magnolia.MEDIATYPEDataTable();
            MEDIATYPETableAdapter MediaTypeAdapter = new MEDIATYPETableAdapter();
            MediaTypeAdapter.Fill(MediaTypes);

            DDList_MediaTypes.DataSource = MediaTypes;
            DDList_MediaTypes.DataTextField = MediaTypes.ValueColumn.ColumnName;
            DDList_MediaTypes.DataValueField = MediaTypes.IDColumn.ColumnName;
            DDList_MediaTypes.DataBind();

            ListItem mediaTypeDefault = new ListItem();
            mediaTypeDefault.Value = "0";
            mediaTypeDefault.Text = "All Media Types";
            DDList_MediaTypes.Items.Insert(0, mediaTypeDefault);
            DDList_MediaTypes.SelectedIndex = -1;
        }
    }

    protected void Btn_RunReport_Click(object sender, EventArgs e)
    {
        Lbl_Alerts.Visible = false;
        Lbl_Messages.Visible = false;
        GridView_Results.Visible = false;

        if ((TextBox_DateFrom.Text.Trim().Length == 0)
                || (TextBox_DateTo.Text.Trim().Length == 0))
        {
            Lbl_Alerts.Text = "&#149; Start and end dates are both required fields";
            Lbl_Alerts.Style.Add("color", "Red");
            Lbl_Messages.Text = "Please provide your report parameters and click \"Run Report\" to view the results";
            Lbl_Messages.Visible = true;
            Lbl_Alerts.Visible = true;
            return;
        }

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SuperSignConnectionString"].ConnectionString);
        try
        {
            // Open connection to db
            conn.Open();

            string sProc = "sproc_WEB_ReportSignUsageByCustomer";
            SqlCommand cmd = new SqlCommand(sProc, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            // set paramter
            DbParameter param = cmd.CreateParameter();
            param.ParameterName = "@DateStart";
            param.Value = Convert.ToDateTime(TextBox_DateFrom.Text);
            param.DbType = DbType.DateTime;
            cmd.Parameters.Add(param);

            // set paramter
            param = cmd.CreateParameter();
            param.ParameterName = "@DateEnd";
            param.Value = Convert.ToDateTime(TextBox_DateTo.Text);
            param.DbType = DbType.DateTime;
            cmd.Parameters.Add(param);

             // set paramter
            param = cmd.CreateParameter();
            param.ParameterName = "@Property";
            param.Value = Convert.ToInt32(DDList_Properties.SelectedValue);
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            //// set paramter
            //param = cmd.CreateParameter();
            //param.ParameterName = "@ClientID";
            //param.Value = Convert.ToInt32(DDList_Clients.SelectedValue);
            //param.DbType = DbType.Int32;
            //cmd.Parameters.Add(param);

            // set paramter
            param = cmd.CreateParameter();
            param.ParameterName = "@Channel";
            param.Value = Convert.ToInt32(DDList_Channels.SelectedValue);
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            // set paramter
            param = cmd.CreateParameter();
            param.ParameterName = "@MediaTypeID";
            param.Value = Convert.ToInt32(DDList_MediaTypes.SelectedValue);
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            // set paramter
            param = cmd.CreateParameter();
            param.ParameterName = "@DurationStart";
            if (TextBox_DurationFrom.Text.Length == 0)
            {
                param.Value = null;
            }
            else
            {
                param.Value = Convert.ToDateTime(TextBox_DurationFrom.Text);
            }
            param.DbType = DbType.DateTime;
            cmd.Parameters.Add(param);

            // set paramter
            param = cmd.CreateParameter();
            param.ParameterName = "@DurationEnd";
            if (TextBox_DurationTo.Text.Length == 0)
            {
                param.Value = null;
            }
            else
            {
                param.Value = Convert.ToDateTime(TextBox_DurationTo.Text);
            }
            param.DbType = DbType.DateTime;
            cmd.Parameters.Add(param);

            // execute the stored procedure and return the results
            dtResults.Columns.Clear();
            dtResults.Rows.Clear();

            SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
            sqlAdapter.Fill(dtResults);

            if (dtResults.Rows.Count == 0)
            {
                Lbl_ReportTitle.Visible = false;
                Lbl_Messages.Text = "The given report parameters returned no results";
                Lbl_Messages.Visible = true;
                Lbl_Messages.Style.Add("display", "block");
                GridView_Results.Visible = false;
                Btn_Print.Enabled = false;
                Btn_ExportCSV.Enabled = false;
            }
            else
            {
                Lbl_ReportTitle.Text = "ABC SuperSign Post-Run Report<br />";
                Lbl_ReportTitle.Text += TextBox_DateFrom.Text + " - " + TextBox_DateTo.Text + "<br /><br />";

                GridViewHelper helper = new GridViewHelper(GridView_Results);
                helper.RegisterGroup("Property", true, true);
                helper.RegisterGroup("Client", true, true);
                helper.GroupHeader += new GroupEvent(helper_GroupHeader);
                helper.RegisterSummary("Times Played", SummaryOperation.Sum, "Property");
                helper.RegisterSummary("Total Minutes", SummaryOperation.Sum, "Property");
 
                helper.GroupSummary += new GroupEvent(helper_GroupSummary);

                GridView_Results.DataSource = dtResults;
                GridView_Results.DataBind();

                // get and display totals
                int timesPlayedTotal = 0;
                decimal totalMinutesTotal = 0;
                for (int i = 0; i < GridView_Results.Rows.Count; i++)
                {
                    timesPlayedTotal += Convert.ToInt32(GridView_Results.Rows[i].Cells[4].Text);
                    totalMinutesTotal += Convert.ToDecimal(GridView_Results.Rows[i].Cells[5].Text);
                }

                //GridView_Results.FooterRow.Cells[0].Style["colspan"] = "4";
                GridView_Results.FooterRow.Style["background-color"] = "LightGrey";
                GridView_Results.FooterRow.Cells[3].Style["text-align"] = "right";
                GridView_Results.FooterRow.Cells[3].Style["font-weight"] = "bold";
                GridView_Results.FooterRow.Cells[3].Text = "Overall Total:";
                GridView_Results.FooterRow.Cells[4].Text = Convert.ToString(timesPlayedTotal);
                GridView_Results.FooterRow.Cells[5].Text = Convert.ToString(totalMinutesTotal);

                Lbl_ReportTitle.Visible = true;

                GridView_Results.Visible = true;

                //Lbl_Messages.Visible = false;
                Lbl_Messages.Style.Add("display", "none");
                Btn_Print.Enabled = true;
                Btn_ExportCSV.Enabled = true;
            }
        }
        catch (Exception ex)
        {
            String err = ex.Message;
            Lbl_Messages.Text = "The report returned an error.  Please check your parameters and try again";
            Lbl_Messages.Visible = true;
        }
        finally
        {
            conn.Close();
        }

    }

    protected void helper_GroupHeader(string groupName, object[] values, GridViewRow row)
    {
        row.Style["font-size"] = "13px";
        row.Style["font-weight"] = "bold";
        if (groupName == "Property")
        {
            row.BackColor = System.Drawing.Color.LightGray;
            row.Cells[0].Text = row.Cells[0].Text;
        }
        else if (groupName == "Client")
        {
            row.BackColor = System.Drawing.Color.FromArgb(236, 236, 236);
            row.Cells[0].Text = "&nbsp;&nbsp;&nbsp;&nbsp;" + row.Cells[0].Text;
        }
    }

    private void helper_GroupSummary(string groupName, object[] values, GridViewRow row)
    {
        row.Cells[0].HorizontalAlign = HorizontalAlign.Right;
        row.Cells[0].Style["font-weight"] = "bold";
        row.Cells[0].Text = "Totals:";
    }

    protected void Btn_Reset_Click(object sender, EventArgs e)
    {
        Lbl_Alerts.Visible = false;
        TextBox_DateFrom.Text = "";
        TextBox_DateTo.Text = "";
        Lbl_TimeFromAlert.Visible = false;
        Lbl_TimeToAlert.Visible = false;
        //TextBox_TimeFrom.Text = "";
        //TextBox_TimeTo.Text = "";
        
        DDList_Channels.SelectedIndex = -1;
        //DDList_Clients.SelectedIndex = -1;
        DDList_Properties.SelectedIndex = -1;
        DDList_MediaTypes.SelectedIndex = -1;
        TextBox_DurationFrom.Text = "";
        TextBox_DurationTo.Text = "";
    }

    protected void WriteReportToFile()
    {
        // Create the file to which data will be exported
        String FileName = Convert.ToString(Page.Title) + ".doc";
        String ExportPath = Server.MapPath("Exports");
        String FilePath = ExportPath + "\\" + FileName;
        if (File.Exists(FilePath))
        {
            File.Delete(FilePath);
        }
        StreamWriter sw = new StreamWriter(FilePath, false);

        // Get Report From Page string
        String startIndexStr = "<div class=\"reportResults\">";
        String endIndexStr = "</table>";

        int startIndex = pageResultStr.IndexOf(startIndexStr);
        int endIndex = pageResultStr.IndexOf(endIndexStr);
        reportResultStr = pageResultStr.Substring(startIndex, endIndex - startIndex + 8);
        reportResultStr += "</div></div>";

        sw.Write(reportResultStr);
        sw.Close();
    }
    protected void Btn_ExportCSV_Click(object sender, EventArgs e)
    {

        WriteReportToFile();
        //ExportGridToCSV();

        Lbl_ReportTitle.Text = "ABC SuperSign Post-Run Report<br />";
        Lbl_ReportTitle.Text += TextBox_DateFrom.Text + " - " + TextBox_DateTo.Text + "<br /><br />";

        GridViewHelper helper = new GridViewHelper(GridView_Results);
        helper.RegisterGroup("Property", true, true);
        helper.RegisterGroup("Client", true, true);
        helper.GroupHeader += new GroupEvent(helper_GroupHeader);
        helper.RegisterSummary("Times Played", SummaryOperation.Sum, "Property");
        helper.RegisterSummary("Total Minutes", SummaryOperation.Sum, "Property");

        helper.GroupSummary += new GroupEvent(helper_GroupSummary);

        GridView_Results.DataSource = dtResults;
        GridView_Results.DataBind();

        // get and display totals
        int timesPlayedTotal = 0;
        decimal totalMinutesTotal = 0;
        for (int i = 0; i < GridView_Results.Rows.Count; i++)
        {
            timesPlayedTotal += Convert.ToInt32(GridView_Results.Rows[i].Cells[4].Text);
            totalMinutesTotal += Convert.ToDecimal(GridView_Results.Rows[i].Cells[5].Text);
        }

        //GridView_Results.FooterRow.Cells[0].Style["colspan"] = "4";
        GridView_Results.FooterRow.Style["background-color"] = "LightGrey";
        GridView_Results.FooterRow.Cells[3].Style["text-align"] = "right";
        GridView_Results.FooterRow.Cells[3].Style["font-weight"] = "bold";
        GridView_Results.FooterRow.Cells[3].Text = "Overall Total:";
        GridView_Results.FooterRow.Cells[4].Text = Convert.ToString(timesPlayedTotal);
        GridView_Results.FooterRow.Cells[5].Text = Convert.ToString(totalMinutesTotal);

        Lbl_ReportTitle.Visible = true;

        GridView_Results.Visible = true;

        //Lbl_Messages.Visible = false;
        Lbl_Messages.Style.Add("display", "none");
        Btn_Print.Enabled = true;
        Btn_ExportCSV.Enabled = true;

    }
    protected void DDList_Properties_SelectedIndexChanged(object sender, EventArgs e)
    {
        //FilterClients(0);
    }
}
